package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.tomcat.jdbc.pool.JdbcInterceptor;

import com.sun.org.apache.bcel.internal.generic.AALOAD;

import entity.Train;

public class TrainManageDAO {

	//插入车次
	public static int insert(Train train) throws Exception{
		String sql = "Insert train (trainid,start,end,time,yzprice,rzprice,ywprice,rwprice,root) values ('"
	+train.getTrainid()+"','"+train.getStart()+"','"+train.getEnd()
	+"','"+train.getTime()+"','"+train.getYzprice()+"','"+train.getRzprice()+"','"
	+train.getYwprice()+"','"+train.getRwprice()+"','"+train.getRoot()+"');";
		System.out.println(sql);
		return JDBCConnect.executeUpdate(sql);
		//return 0;
	}
	
	//更新车次
	/*public static int update(Train train) throws SQLException{
		String sql = "Update train set start = ?,end = Where trainid = ?";
		return JDBCConnect.executeUpdate(sql,train.getStart(),train.getEnd(),train);
	}*/
	//新更新操作
	public static int update(Train train) throws SQLException{
		String sql = "Update train set start='"+train.getStart()
		+"',end='"+train.getEnd()+"',time='"+train.getTime()
		+"',yzprice='"+train.getYzprice()+"',rzprice='"+train.getRzprice()
		+"',ywprice='"+train.getYwprice()+"',rwprice='"+train.getRwprice()
		+"',root='"+train.getRoot()+"' WHERE trainid='"+train.getTrainid()+"';";
		/*String sql = "Update train set start,end,time,yzprice,rzprice,ywprice,rwprice,root where trainid=;"
				+train.getTrainid()+"','"+train.getStart()+"','"+train.getEnd()
				+"','"+train.getTime()+"','"+train.getYzprice()+"','"+train.getRzprice()+"','"
				+train.getYwprice()+"','"+train.getRwprice()+"','"+train.getRoot()+"');";*/
					System.out.println(sql);
		return JDBCConnect.executeUpdate(sql);
	}
	
	//删除操作
	public static int delete(String id) throws SQLException{
		String sql = "Delete from train where trainid = ?";
		return JDBCConnect.executeUpdate(sql,id);
	}
	
	//查找记录，某车次
	public static Train find(String id) throws SQLException{
		String sql = "Select * from train where trainid = ? ";
		Connection conn = null;
		PreparedStatement preStat = null;
		ResultSet rs = null;
		try {
			//连接
			conn = JDBCConnect.getConnection();
			preStat = conn.prepareStatement(sql);
			preStat.setString(1, id);
			rs = preStat.executeQuery();
			if(rs.next()){
				Train train = new Train();
				train.setTrainid(rs.getString("trainid"));
				train.setStart(rs.getString("Start"));
				train.setEnd(rs.getString("end"));
				train.setTime(rs.getString("time"));
				train.setYzprice(rs.getFloat("yzprice"));
				train.setYwprice(rs.getFloat("ywprice"));
				train.setRzprice(rs.getFloat("rzprice"));
				train.setRwprice(rs.getFloat("rwprice"));
				train.setRoot(rs.getString("root"));
				return train;
			}else{
				return null;
			}
		} finally {
			// TODO: handle exception
			if(rs!=null){
				rs.close();
			}
			if(preStat!=null){
				preStat.cancel();
			}
			if(conn!=null){
				conn.close();
			}
		}
	}
	
	//查询记录 起始站，达到站
	public static List<Train> findStartEnd(String start,String end) throws SQLException{
		List<Train> list = new ArrayList<Train>();
		String sql = null;
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		//判断sql语句
		if(start=="" && end == ""){
			sql = "Select * from train;";
		}else if(end==""){
			sql = "Select * from train where start='"+start+"';";
		}else if(start==""){
			sql = "Select * from train where end='"+end+"';";
		}else{
			sql = "Select * from train where start='"+start+"' and end='"+end+"';";
		}
		//执行
		try {
			//
			conn = JDBCConnect.getConnection();
			stat = conn.createStatement();
			System.out.println(start+" "+end);
			System.out.println(sql);
			rs = stat.executeQuery(sql);
			//
			while(rs.next()){
				Train train = new Train();
				train.setTrainid(rs.getString("trainid"));
				train.setStart(rs.getString("Start"));
				train.setEnd(rs.getString("end"));
				train.setTime(rs.getString("time"));
				train.setYzprice(rs.getFloat("yzprice"));
				train.setYwprice(rs.getFloat("ywprice"));
				train.setRzprice(rs.getFloat("rzprice"));
				train.setRwprice(rs.getFloat("rwprice"));
				train.setRoot(rs.getString("root"));
				list.add(train);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			if(rs!=null){
				rs.close();
			}
			if(stat!=null){
				stat.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return list;
	}
	
	//
	public static List<Train> listAll() throws SQLException{
		List<Train> list = new ArrayList<Train>();
		String sql = "Select * from train";
		Connection conn = null;
		PreparedStatement preStat = null;
		ResultSet rs = null;
		try {
			conn = JDBCConnect.getConnection();
			preStat = conn.prepareStatement(sql);
			rs = preStat.executeQuery();
			while(rs.next()){
				//
				Train train = new Train();
				train.setTrainid(rs.getString("trainid"));
				train.setStart(rs.getString("Start"));
				train.setEnd(rs.getString("end"));
				train.setTime(rs.getString("time"));
				train.setYzprice(rs.getFloat("yzprice"));
				train.setYwprice(rs.getFloat("ywprice"));
				train.setRzprice(rs.getFloat("rzprice"));
				train.setRwprice(rs.getFloat("rwprice"));
				train.setRoot(rs.getString("root"));
				list.add(train);
			}
		} finally{
			if(rs!=null){
				rs.close();
			}
			if(preStat!=null){
				preStat.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return list;
	}
}
